package com.taru.order.util;


import com.taru.order.common.RowMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class DbUtil {
    private static final String JDBC_DRIVER="com.mysql.jdbc.Driver";
    private static final String JDBC_URL="jdbc:mysql://localhost:3306/item_one?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
    private static final String JDBC_USERNAME="root";
    private static final String JDBC_PASSWORD="admin";
    private static ThreadLocal<Connection> local =new ThreadLocal<Connection>();
    /**
     * 加载驱动
     */
    static{
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败");
        }
    }

    /**
     * 打开连接
     * @return
     */
    public static Connection openConnectin(){
        Connection conn=local.get();
        try {
            if(conn==null||conn.isClosed()){
                conn= DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
                local.set(conn);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 开启事务
     */
    public static void beginTransaction(){
        Connection conn=openConnectin();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 提交
     */
    public static void commit(){
        Connection conn=local.get();
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 回滚
     */
    public static void rollback(){
        Connection conn=local.get();
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行DML操作
     * @param sql
     * @param params
     * @return
     */
    public static int update(String sql,Object...params){
        PreparedStatement pst=null;
        Connection conn= openConnectin();
        int row=0;
        try {
            pst = conn.prepareStatement(sql);
            if(params!=null && params.length>0){
                for(int i=0;i<params.length;i++){
                    pst.setObject(i+1,params[i]);
                }
            }
            row=pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("DML异常",e) ;
        }finally{
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return row;
    }

    /**
     * 执行查询操作
     * @param sql
     * @param params
     * @return
     */
    public static List<Map<String,Object>> query(String sql, Object...params){
        PreparedStatement pst=null;
        Connection conn= openConnectin();
        ResultSet rs=null;
        List<Map<String,Object>> list =new ArrayList<Map<String,Object>>();
        try {
            pst = conn.prepareStatement(sql);
            if(params!=null && params.length>0){
                for(int i=0;i<params.length;i++){
                    pst.setObject(i+1,params[i]);
                }
            }
            rs=pst.executeQuery();
            //首先获取rs中的元数据
            ResultSetMetaData rsmd=rs.getMetaData();//获取总列数
            int count=rsmd.getColumnCount();
            while(rs.next()){
                Map<String,Object> row =new HashMap<String,Object>();
                //具体解析
                for(int i=0;i<count;i++){
                    String columnLable=rsmd.getColumnLabel(i+1);
                    row.put(columnLable,rs.getObject(columnLable));
                }
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询异常",e) ;
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * RowMapper实现查询
     * @param sql
     * @param rowMapper
     * @param params
     * @param <T>
     * @return
     */
    public static<T> List<T> query(String sql, RowMapper<T> rowMapper, Object...params){
        PreparedStatement pst=null;
        Connection conn= openConnectin();
        ResultSet rs=null;
        List<T> list =new ArrayList<T>();
        try {
            pst = conn.prepareStatement(sql);
            if(params!=null && params.length>0){
                for(int i=0;i<params.length;i++){
                    pst.setObject(i+1,params[i]);
                }
            }
            rs=pst.executeQuery();
            //首先获取rs中的元数据

            while(rs.next()){
                T entity= rowMapper.mapping(rs) ;
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询异常",e) ;
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    /**
     * 查询数据总数
     */
    public static Long getTotalCount(String sql){
        long count=-1;
        Connection conn=openConnectin();
        PreparedStatement pst=null;
        ResultSet resultSet=null;
        try {
            pst=conn.prepareStatement(sql);
            resultSet=pst.executeQuery();
            if(resultSet.next()){
                count=resultSet.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(pst!=null){
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return count;
    }
    /**
     * 关闭连接
     */
    public static void close(){
        Connection conn=local.get();
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            local.remove();
        }
    }
}


